package common.spring.jdbc;

import common.pojo.Page;
import common.utils.MyMapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;

/**
 * Created by luozongsheng on 08/10/2016.
 */
public abstract class SQLHelper {

    protected JdbcTemplate jdbcTemplate;

    protected DataSource dataSource;

    private Map<String,SimpleJdbcInsert> jdbcInsertMap=new HashMap<>();

    public DataSource getDataSource() {
        return dataSource;
    }

    public int delete(String sql){
        return jdbcTemplate.update(sql);
    }

    public int delete(String sql, Object[] args){
        return jdbcTemplate.update(sql,args);
    }

    public int update(String sql){
        return jdbcTemplate.update(sql);
    }

    public int update(String sql, Object[] args){
        return jdbcTemplate.update(sql,args);
    }

    public int update(String sql, MapSqlParameterSource params){
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate.update(sql,params);
    }

    public int insert(String sql){
        return jdbcTemplate.update(sql);
    }

    public int insert(String sql, Object[] params){
        return jdbcTemplate.update(sql, params);
    }

    public int[] batchUpdate(String sql, List<Object[]> argList){
        return jdbcTemplate.batchUpdate(sql, argList);
    }

    public int simpleInsert(String tableName, Map<String,Object> fieldValue){
        SimpleJdbcInsert insert=getSimpleJdbcInsert(tableName,null);
        return insert.execute(fieldValue);
    }

    public int simpleUpdate(String tableName, Map<String,Object> fieldValue){
        return this.simpleUpdate(tableName, fieldValue, null);
    }

    public int simpleUpdate(String tableName, Map<String,Object> fieldValue, Map<String,Object> where){
        StringBuilder sql=new StringBuilder(String.format("update %s set ",tableName));
        List params=new ArrayList<>();
        for(String key:fieldValue.keySet()){
            if(key.indexOf("?")==-1){
                sql.append(String.format("  %s = ? ,",key));
                params.add(fieldValue.get(key));
            }else{
                sql.append(String.format(" %s ,",key));
                params.add(fieldValue.get(key));
            }

        }

        sql.setLength(sql.length()-1);

        if(where!=null){
            sql.append(" where ");
            for(String key:where.keySet()){
                sql.append(String.format(" %s = ? and",key));
                params.add(where.get(key));
            }
            sql.setLength(sql.length()-3);
        }

        return this.update(sql.toString(), params.toArray());
    }

    public Long queryForNumber(String sql, Object[] args){
        return jdbcTemplate.queryForObject(sql, args, Long.class);
    }

    public Double queryForDouble(String sql, Object[] args){
        return jdbcTemplate.queryForObject(sql, args, Double.class);
    }

    public <T> T queryForNumber(String sql, Class<T> type, Object[] args){
        return jdbcTemplate.queryForObject(sql, args, type);
    }
    public Long queryForNumber(String sql){
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    public Map<String,Object> queryForMap(String sql, Object[] args){
        try{
            return jdbcTemplate.queryForMap(sql,args);
        }catch(EmptyResultDataAccessException e){
            return null;
        }
    }

    public Map<String,Object> queryForMap(String sql){
        try{
            return jdbcTemplate.queryForMap(sql);
        }catch(EmptyResultDataAccessException e){
            return null;
        }
    }

    public <T> T queryForBean(String sql, Class<T> type){
        try{
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(type));
        }catch(EmptyResultDataAccessException e){
            return null;
        }
    }

    public <T> T queryForBean(String sql, Class<T> type, Object[] args){
        try{
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(type), args);
        }catch(EmptyResultDataAccessException e){
            return null;
        }
    }

    public List<Map<String,Object>> queryForListMap(String sql){
        return jdbcTemplate.queryForList(sql);
    }

    public List<Map<String,Object>> queryForListMap(String sql, MapSqlParameterSource params){
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate.queryForList(sql,params);
    }

    public List<Map<String,Object>> queryForListMap(String sql, int top){
        sql+=" limit 0,?";
        return jdbcTemplate.queryForList(sql, new Object[]{top});
    }

    public List<Map<String,Object>> queryForListMap(String sql, Object[] args){
        return jdbcTemplate.queryForList(sql, args);
    }

    public List<Map<String,Object>> queryForListMap(String sql, Object[] args, int top){
        sql+=" limit 0,?";
        if(args==null){
            args=new Object[]{top};
        }else{
            args= ArrayUtils.add(args, top);
        }
        return jdbcTemplate.queryForList(sql, args);
    }

    public <T> List<T> queryForListBean(String sql, Class<T> type, Object[] args){
//        List params = null;
//        try {
//            type.getDeclaredField("deptIds");
//            params = new ArrayList();
//            HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
//            params.add(request.getSession().getAttribute("LOGIN_USER_DEPT_ID_INFO"));
//            for (Object arg : args) {
//                params.add(arg);
//            }
//            sql = "select * from "+sql+ "where "
//            sql.replaceFirst("where","where INSTR(deptIds, ?) > 0 and ");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(type), args);
    }

    public <T> List<T> queryForListBeanByParams(String sql, Class<T> type, MapSqlParameterSource params){
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate.query(sql,params,BeanPropertyRowMapper.newInstance(type));
    }


    public List queryForListBeanByParams(String sql, MapSqlParameterSource params){
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate.queryForList(sql,params);
    }

    public int updateBeanByParams(String sql, MapSqlParameterSource params){
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        return namedParameterJdbcTemplate.update(sql,params);
    }

    public <T> List<T> queryForqueryForListBeanListBean(String sql, Class<T> type, Object[] args, int top){
        sql+=" limit 0,?";
        if(args==null){
            args=new Object[]{top};
        }else{
            args= ArrayUtils.add(args,top);
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(type), args);
    }

    public <T> List<T> queryForListBean(String sql, Class<T> type){
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(type));
    }

    public <T> List<T> queryForListBean(String sql, Class<T> type, int top){
        sql+=" limit 0,?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(type),new Object[]{top});
    }

    public Page<Map> queryForPageMap(String sql, int curPage, int pageSize){
        return queryForPageBean(sql, Map.class, curPage, pageSize);
    }

    public Page<Map> queryForPageMap(String sql, Object[] params, int curPage, int pageSize){
        return queryForPageBean(sql, params, Map.class, curPage, pageSize);
    }

    public abstract <T> Page<T> queryForPageBean(String sql, Class<T> type, int curPage, int pageSize);

    public abstract <T> Page<T> queryForPageBean(String sql, Object[] params, Class<T> type, int curPage, int pageSize);

    public Set queryListToSet(String sql){
        return queryListToSet(sql,null);
    }
    public Set queryListToSet(String sql, Object[] params){
        final Set set=new HashSet();
        jdbcTemplate.query(sql,params,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                set.add(resultSet.getObject(1));
            }
        });
        return set;
    }

    public Map queryListToMap(String sql){
        return queryListToMap(sql,null);
    }
    public Map queryListToMap(String sql, Object[] params){
        final Map map=new HashMap();
        jdbcTemplate.query(sql,params,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                ResultSetMetaData metaData = resultSet.getMetaData() ;
                if(metaData.getColumnCount()<2){
                    return;
                }
                map.put(resultSet.getObject(1), resultSet.getObject(2));
            }
        });
        return map;
    }

    public Map queryMapListByColumnKey(String sql){
        return queryMapListByColumnKey(sql,new Object[0]);
    }

    public Map queryMapListByColumnKey(String sql, Object[] params){
        final Map<String,List> map=new HashMap();
        jdbcTemplate.query(sql,params,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount=metaData.getColumnCount();
                for(int i=0;i<columnCount;i++){
                    String columnName=metaData.getColumnName(i+1);
                    List list=map.get(columnName);
                    if(list==null){
                        list=new ArrayList();
                        map.put(columnName,list);
                    }
                    int type=metaData.getColumnType(i+1);
                    Object obj;
                    switch (type){
                        case Types.BIGINT:
                        case Types.SMALLINT:
                        case Types.INTEGER:
                            obj=resultSet.getLong(i+1);
                            break;
                        case Types.FLOAT:
                        case Types.DOUBLE:
                            obj=resultSet.getDouble(i+1);
                            break;
                        case Types.VARCHAR:
                        case Types.LONGVARCHAR:
                        case Types.LONGNVARCHAR:
                        case Types.NVARCHAR:
                            obj=resultSet.getString(i+1);
                            break;
                        case Types.DATE:
                        case Types.TIME:
                        case Types.TIMESTAMP:
                            obj=resultSet.getDate(i+1);
                            break;
                        default:
                            obj=resultSet.getObject(i+1);
                            break;
                    }
                    list.add(obj);
                }
            }
        });
        return map;
    }

    public <T> List<T> queryListByFirstColumn(String sql){
        return queryListByFirstColumn(sql,new Object[0]);
    }

    public <T> List<T> queryListByFirstColumn(String sql, Object[] params){
        final List<T> list=new ArrayList<>();
        jdbcTemplate.query(sql,params,new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                T obj;
                ResultSetMetaData metaData = resultSet.getMetaData() ;
                int type=metaData.getColumnType(1);
                switch (type){
                    case Types.BIGINT:
                    case Types.SMALLINT:
                    case Types.INTEGER:
                        obj=(T)((Long)resultSet.getLong(1));
                        break;
                    case Types.FLOAT:
                    case Types.DOUBLE:
                        obj=(T)((Double)resultSet.getDouble(1));
                        break;
                    case Types.VARCHAR:
                    case Types.LONGVARCHAR:
                    case Types.LONGNVARCHAR:
                    case Types.NVARCHAR:
                        obj=(T)resultSet.getString(1);
                        break;
                    case Types.DATE:
                    case Types.TIME:
                    case Types.TIMESTAMP:
                        obj=(T)resultSet.getDate(1);
                        break;
                    default:
                        obj=(T)resultSet.getObject(1);
                        break;
                }
                list.add(obj);
            }
        });
        return list;
    }

    public int simpleInsert(String tableName, String autoField, Map<String,Object> fieldValue){
        SimpleJdbcInsert insert=this.getSimpleJdbcInsert(tableName,autoField);
        return insert.execute(fieldValue);
    }

    public Number simpleInsertReturnAutoField(String tableName, String autoField, Map<String,Object> fieldValue){
        SimpleJdbcInsert insert=this.getSimpleJdbcInsert(tableName,autoField);
        return insert.executeAndReturnKey(fieldValue);
    }

    public Number simpleInsertReturnAutoField(String tableName, Map<String, Object> fieldValue) {
        SimpleJdbcInsert insert = this.getSimpleJdbcInsert(tableName, "id");
        return insert.executeAndReturnKey(fieldValue);
    }

    public <T> boolean simpleInsertBatchByList(String tableName, List<T> list, Boolean isSetVal) {
        boolean b = false;
        Map[] maps = new Map[list.size()];
        Map addMap = null;
        for (int i = 0;i<list.size();i++){
            T t = list.get(i);
            addMap= MyMapUtils.beanToMap(t,isSetVal);
            maps[i]=addMap;

        }
        SimpleJdbcInsert insert = this.getSimpleJdbcInsert(tableName, "id");
        int[] retult = insert.executeBatch(maps);
        if(retult.length>0){
            b = true;
        }
        return b;
    }

    public <T> boolean simpleInsertBatchByListNotAutoField(String tableName, List<T> list, Boolean isSetVal) {
        boolean b = false;
        Map[] maps = new Map[list.size()];
        Map addMap = null;
        for (int i = 0;i<list.size();i++){
            T t = list.get(i);
            addMap= MyMapUtils.beanToMap(t,isSetVal);
            maps[i]=addMap;

        }
        SimpleJdbcInsert insert = this.getSimpleJdbcInsert(tableName,"");
        int[] retult = insert.executeBatch(maps);
        if(retult.length>0){
            b = true;
        }
        return b;
    }

    public <T> boolean simpleInsertBatchByMaps(String tableName, Map[] maps) {
        boolean b = false;
        SimpleJdbcInsert insert = this.getSimpleJdbcInsert(tableName, "id");
        int[] retult = insert.executeBatch(maps);
        if(retult.length>0){
            b = true;
        }
        return b;
    }

    private SimpleJdbcInsert getSimpleJdbcInsert(String tableName, String autoField){
        String key= String.format("%s[%s]",tableName,autoField);
        SimpleJdbcInsert insert=jdbcInsertMap.get(key);
        if(insert==null){
            insert=new SimpleJdbcInsert(jdbcTemplate);
            insert.withTableName(tableName);
            if(StringUtils.isNotBlank(autoField)){
                insert.usingGeneratedKeyColumns(autoField);
            }
            jdbcInsertMap.put(key,insert);
        }
        return insert;
    }

    public void setDataSource(DataSource dataSource) {
        jdbcTemplate=new JdbcTemplate(dataSource);
        this.dataSource = dataSource;
    }

    /**
     * 分页查询list
     * @param sql
     * @param params
     * @param type
     * @param curPage
     * @param pageSize
     * @param <T>
     * @return
     */
    public <T> List<T> queryForListBean(String sql, Object[] params, Class<T> type, int curPage, int pageSize){
        sql += String.format(" limit %s,%s",(curPage-1)*pageSize,pageSize);
        return jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(type),params);
    }

    /**
     * 查询list
     * @param sql
     * @param params
     * @param type
     * @param <T>
     * @return
     */
    public <T> List<T> queryForListBean(String sql, Object[] params, Class<T> type){
        return jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(type),params);
    }
}
